Skip to main content

Casts

Use AS keyword to convert selection to the target object type.

tip

Please note, that it is possible to use a keyword as with both aliases and casts. The following principle applies:

  1. AS works as alias in case a variable after AS does not match any existing object and such a variable is used to name a column.
  2. AS works as cast in case a variable after AS matches an existing object. Embrace a variable name after AS in single quotes to treat it as alias for column naming.
Casting examples
#select Info objects of a LimitOrderInfo type and convert output objects to LimitOrderInfo object type*/
SELECT order.info AS deltix.orders.LimitOrderInfo

#take a polymorphic object Entry and convert it to the fixed type object L1Entry
#query returns NULL in case conversion is not possible.
SELECT entry AS deltix.entries.L1Entry FROM packages
ARRAY JOIN entries AS entry

#in this case we convert Entry object to a polymorphic object type that includes objects deltix.entries.L1Entry and deltix.entries.L2Entry
#object keyword in this case allows us to specify a comma-separated list of objects.
SELECT entry AS object(deltix.entries.L1Entry, deltix.entries.L2Entry) FROM packages
ARRAY JOIN entries AS entry

In previous examples we used existing object types. For example deltix.entries.L1Entry and deltix.entries.L2Entry are existing object types in the Entry polymorphic object. Therefore, cast to any of them would reduce the source object. Nevertheless, you can cast to not existing object types and, thus, extend the source object.

Casting to a new object type
SELECT entry AS object(deltix.entries.L1Entry, deltix.entries.L2Entry, deltix.entries.TradeEntry) FROM packages
ARRAY JOIN entries AS entry

In this example query will return NULL for all deltix.entries.TradeEntry, because Entry object does not have this object type. Hence, fields with NULL values will have deltix.entries.TradeEntry object type.

Cast Arrays​

It is as well possible to use cast with arrays. Use cats to create a fixed type and polymorphic arrays.

Using Casting with arrays
#takes a polymorphic array Entries and creates a fixed type array with only deltix.entries.TradeEntry type of array, returns NULL for other array types
SELECT entries AS array(deltix.entries.TradeEntry) FROM packages

#filter out NULL values
SELECT entries AS array(deltix.entries.TradeEntry)[THIS IS NOT NULL] FROM packages

#cast to a polymorphic array
SELECT entries AS array(deltix.entries.L1Entry, deltix.entries.L2Entry) FROM packages

#select Key fields as an array just from FixAttribute types of arrays and cast them to FixAttribute array type
SELECT (entries.attributes[THIS IS deltix.FixAttribute] AS array(deltix.FixAttribute).key FROM packages

#cast arrays of entries to L1entry format*/
SELECT 
entry[side == ASK].price AS askPrice, 
entry[side == ASK].size AS askSize, 
entry[side == BID].price AS bidPrice, 
entry[side == BID].size AS bidSize
FROM kraken 
ARRAY JOIN (entries AS array(deltix.timebase.api.messages.universal.L1entry))[THIS IS NOT NULL] AS entry

#in this example we select Prices from Entries array and cast it to TradeEntry object type
SELECT (entries AS array(deltix.timebase.api.messages.universal.TradeEntry)).price AS Price FROM binance2
WHERE size(entries[THIS IS deltix.timebase.api.messages.universal.TradeEntry])>0

Cast Primitives​

Use cast to convert primitives data types.

tip

Please note, that casting from higher to lower precision types causes data loss. For example cast from Int16 to Int8.

Casting primitive data types
# Cast a byte field to all possible types
SELECT 
byteField AS INT8, byteField AS INT16, byteField AS INT32, byteField AS INT64, byteField as DECIMAL, byteField AS FLOAT32, byteField AS FLOAT64, 
byteField AS CHAR, byteField AS BOOLEAN, byteField as TIMESTAMP, byteField as VARCHAR
FROM alltypes

# Cast Integer <-> timestamp
# result: 1970-01-01 00:00:01.000, 2000, [1000,2000]
select 1000 as timestamp, '1970-01-01 00:00:02.000'd as int64, ['1970-01-01 00:00:01.000'd, '1970-01-01 00:00:02.000'd] as array(int64)
From\ToINT8INT16INT32INT64FLOAT32FLOAT64DECIMALBOOLEANCHARTIMESTAMPVARCHARENUM
INT8+++++++++++-
INT16+++++++++++-
INT32+++++++++++-
INT64+++++++++++-
FLOAT32+++++++++++-
FLOAT64+++++++++++-
DECIMAL+++++++++++-
BOOLEAN+++++++++++-
CHAR+++++++++++-
TIMESTAMP+++++++++++-
VARCHAR----------+-
ENUM----------++